﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Globalization;
using System.Reflection;
using System.Threading;
using Microsoft.Office.Interop.Excel;
using DataTable = System.Data.DataTable;

namespace FootballProject.Common.CommonLib.Parsers
{
    /// <summary>
    /// Author: Ivan Shikht
    /// </summary>
    public class ExcelInteropParser : IDisposable
    {
        #region Fields
        private readonly BackgroundWorker _backgroundWorker;
        private readonly Application _excelApplication;
        private readonly Workbook _excelWorkbook;

        private readonly string _fileName;
        private ParseEventArgs _parseEventArgs;
        #endregion

        #region ParsingCompleted Event

        public event EventHandler<ParseEventArgs> ParsingCompleted;

        private void InvokeParsingCompleted(ParseEventArgs e)
        {
            var completed = ParsingCompleted;
            if (completed != null) completed(this, e);
        }

        #endregion

        #region Constructor
        public ExcelInteropParser(string fileName)
        {
            _fileName = fileName;
            using (var excelUiLanguageHelper = new ExcelUILanguageHelper())
            {
                //Create Application object
                _excelApplication = new ApplicationClass();

                //Get WorkBook object
                _excelWorkbook = ExcelApplication.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value,
                                                                 Missing.Value,
                                                                 Missing.Value,
                                                                 Missing.Value, Missing.Value, Missing.Value,
                                                                 Missing.Value, Missing.Value,
                                                                 Missing.Value, Missing.Value,
                                                                 Missing.Value, Missing.Value);
            }

            _backgroundWorker = new BackgroundWorker();
            _backgroundWorker.DoWork += BackgroundWorkerDoWork;
            _backgroundWorker.RunWorkerCompleted += BackgroundWorkerRunWorkerCompleted;
        }

        #endregion

        #region Properties

        public Application ExcelApplication
        {
            get { return _excelApplication; }
        }

        public Workbook ExcelWorkbook
        {
            get { return _excelWorkbook; }
        }

        #endregion

        #region Methods

        #region Parse function overload

        public void Parse(int worksheetIndex, bool firstRowHeaders)
        {
            Parse(worksheetIndex, -1, firstRowHeaders);
        }

        public void Parse(int worksheetIndex, int rangeFirstColumn, bool firstRowHeaders)
        {
            Parse(worksheetIndex, rangeFirstColumn, -1, firstRowHeaders);
        }

        public void Parse(int worksheetIndex, int rangeFirstColumn, int rangeFirstRow, bool firstRowHeaders)
        {
            Parse(worksheetIndex, rangeFirstColumn, rangeFirstRow, -1, firstRowHeaders);
        }

        public void Parse(int worksheetIndex, int rangeFirstColumn, int rangeFirstRow, int rangeLastColumn,
                          bool firstRowHeaders)
        {
            Parse(worksheetIndex, rangeFirstColumn, rangeFirstRow, rangeLastColumn, -1, firstRowHeaders);
        }

        public void Parse(int worksheetIndex, int rangeFirstColumn, int rangeFirstRow, int rangeLastColumn,
                          int rangeLastRow, bool firstRowHeaders)
        {
            ParseFunc(worksheetIndex, rangeFirstColumn, rangeFirstRow, rangeLastColumn, rangeLastRow,
                      firstRowHeaders);
        }

        public void Parse(string worksheetName, bool firstRowHeaders)
        {
            Parse(worksheetName, -1, firstRowHeaders);
        }

        public void Parse(string worksheetName, int rangeFirstColumn, bool firstRowHeaders)
        {
            Parse(worksheetName, rangeFirstColumn, -1, firstRowHeaders);
        }

        public void Parse(string worksheetName, int rangeFirstColumn, int rangeFirstRow, bool firstRowHeaders)
        {
            Parse(worksheetName, rangeFirstColumn, rangeFirstRow, -1, firstRowHeaders);
        }

        public void Parse(string worksheetName, int rangeFirstColumn, int rangeFirstRow, int rangeLastColumn,
                          bool firstRowHeaders)
        {
            if (worksheetName == null) throw new ArgumentNullException("worksheetName");
            Parse(worksheetName, rangeFirstColumn, rangeFirstRow, rangeLastColumn, -1, firstRowHeaders);
        }

        public void Parse(string worksheetName, int rangeFirstColumn, int rangeFirstRow, int rangeLastColumn,
                          int rangeLastRow, bool firstRowHeaders)
        {
            ParseFunc(worksheetName, rangeFirstColumn, rangeFirstRow, rangeLastColumn, rangeLastRow,
                      firstRowHeaders);
        }

        private void ParseFunc(object worksheetObj, int rangeFirstColumn, int rangeFirstRow, int rangeLastColumn,
                               int rangeLastRow, bool firstRowHeader)
        {
            var arguments = new ParseEventArgs
                                {
                                    FirstRowHeader = firstRowHeader,
                                    RangeFirstColumn = rangeFirstColumn,
                                    RangeFirstRow = rangeFirstRow,
                                    RangeLastColumn = rangeLastColumn,
                                    RangeLastRow = rangeLastRow,
                                    WorksheetObject = worksheetObj,
                                    Table = new DataTable()
                                };

            _backgroundWorker.RunWorkerAsync(arguments);
        }

        #endregion

        private ParseEventArgs ParseFunc(object arg)
        {
            var arguments = (ParseEventArgs)arg;

            bool firstRowHeader = arguments.FirstRowHeader;
            int firstColumn = arguments.RangeFirstColumn;
            int firstRow = arguments.RangeFirstRow;
            int lastColumn = arguments.RangeLastColumn;
            int lastRow = arguments.RangeLastRow;
            object worksheetObj = arguments.WorksheetObject;
            DataTable dataTable = arguments.Table;

            using (var excelUiLanguageHelper = new ExcelUILanguageHelper())
            {
                Range range;


                //try
                //{
                //Get WorkSheet object 
                var worksheet = (Worksheet)ExcelWorkbook.Sheets[worksheetObj];

                #region Get range

                if (lastColumn == -1)
                {
                    lastColumn = worksheet.UsedRange.Cells.Columns.Count;
                }
                if (lastRow == -1)
                {
                    lastRow = worksheet.UsedRange.Cells.Rows.Count;
                }

                if (firstRow == -1)
                {
                    firstRow = 1;
                }

                if (firstColumn == -1)
                {
                    firstColumn = 1;
                }

                #endregion

                #region Get columns headers data

                if (!firstRowHeader)
                {
                    for (int j = firstColumn; j <= lastColumn; j++)
                    {
                        dataTable.Columns.Add("Column" + j, Type.GetType("System.String"));
                    }
                }
                else
                {
                    for (int j = firstColumn; j <= lastColumn; j++)
                    {
                        range = (Range)worksheet.Cells[firstRow, j];
                        string strValue = range.Text.ToString();
                        DataTableColumnAdd(dataTable, strValue);
                    }
                    firstRow += 1;
                }

                #endregion

                #region Get data from cells

                for (int i = firstRow; i <= lastRow; i++)
                {
                    DataRow dataRow = dataTable.NewRow();
                    for (int j = firstColumn; j <= lastColumn; j++)
                    {
                        range = (Range)worksheet.Cells[i, j];
                        string strValue = range.Text.ToString();
                        dataRow[j - 1] = strValue;
                    }
                    dataTable.Rows.Add(dataRow);
                }

                #endregion
            }
            return arguments;
            // }
            //catch (Exception ex)
            //{
            //    //TODO Handle exception
            //    return null;
            //}
        }

        private void BackgroundWorkerRunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            InvokeParsingCompleted(_parseEventArgs);
        }

        private void BackgroundWorkerDoWork(object sender, DoWorkEventArgs e)
        {
            _parseEventArgs = ParseFunc(e.Argument);
        }

        private static void DataTableColumnAdd(DataTable dataTable, string original)
        {
            DataTableColumnAdd(dataTable, original, original, 0);
        }

        private static void DataTableColumnAdd(DataTable dataTable, string original, string strValue, int iteration)
        {
            if (!dataTable.Columns.Contains(strValue))
            {
                dataTable.Columns.Add(strValue, Type.GetType("System.String"));
            }
            else
            {
                iteration += 1;
                DataTableColumnAdd(dataTable, original, original + iteration, iteration);
            }
        }

        public void CancelParsing()
        {
            _backgroundWorker.CancelAsync();
        }

        public List<string> Sheets
        {
            get
            {
                var list = new List<string>();
                foreach (Worksheet worksheet in _excelWorkbook.Worksheets)
                {
                    list.Add(worksheet.Name);
                }
                return list;
            }
        }

        #endregion

        #region Nested type: ParseEventArgs

        public class ParseEventArgs : EventArgs
        {
            public object WorksheetObject { get; set; }
            public int RangeFirstColumn { get; set; }
            public int RangeFirstRow { get; set; }
            public int RangeLastColumn { get; set; }
            public int RangeLastRow { get; set; }
            public bool FirstRowHeader { get; set; }
            public DataTable Table { get; set; }
        }

        #endregion

        #region Destructor

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        ~ExcelInteropParser()
        {
            // Finalizer calls Dispose(false)
            Dispose(true);
        }

        // The bulk of the clean-up code is implemented in Dispose(bool)
        protected virtual void Dispose(bool disposing)
        {
            if (!disposing) return;
            using (var excelUiLanguageHelper = new ExcelUILanguageHelper())
            {
                _excelWorkbook.Close(false, _fileName, Missing.Value);
                _excelApplication.Quit();
            }
            _backgroundWorker.Dispose();
        }

        #endregion
    }

    internal class ExcelUILanguageHelper : IDisposable
    {
        private readonly CultureInfo _mCurrentCulture;

        public ExcelUILanguageHelper()
        {
            _mCurrentCulture = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
        }

        #region IDisposable Members

        public void Dispose()
        {
            Thread.CurrentThread.CurrentCulture = _mCurrentCulture;
        }

        #endregion
    }
}